package bulk_param.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.swing.JOptionPane;

import bulk_param.modele.OuverturePointModel;


public class OuverturePointDao {
	
	public OuverturePointModel ouverturePointModel;
	private String userid = "arbor";
    private String password = "arbor123";
    private String pilote = "oracle.jdbc.driver.OracleDriver";
    private String url = "jdbc:oracle:thin:@10.1.3.136:1522:cc_cust";      	

	private static Connection con;

     // constructor 
	public OuverturePointDao(){
		this.ouverturePointModel= new OuverturePointModel();
	}

	public Connection getConnection(){
				
		try {
			Class.forName(pilote);	

		} catch(java.lang.ClassNotFoundException e) {
			System.err.print("ClassNotFoundException: ");
			System.err.println(e.getMessage());
		}

		try {
			con = DriverManager.getConnection(url, userid, password);
			System.out.println ("Ouverture Connection");     
		} catch(SQLException ex) {
			System.err.println("SQLException: " + ex.getMessage());
		}
		
		return con;
	}
	
	static  public  void  closeConnection(){         
		System.out.println ("Colse DBConnection.");          
		        
				
		try {
			con.close();             
			System.out.println ("Fermeture Connection");        
			}         
		catch (Exception ex) {             
			System.out.println ("ERROR :"+ex.getMessage());             
			ex.printStackTrace();        
			}    
		} 
	
	//Verification de l'existance d'un point
	public boolean isPointExist(String indicatif,String pointZABPQ){
		getConnection();
			boolean isPointExist =false;
			try{
				String sql="SELECT point FROM USAGE_POINTS "
						+"WHERE point = '"+indicatif+pointZABPQ+"'  "
						+"AND inactive_dt is null";
				
				// Create a prepared statement
	 			Statement s = con.createStatement();

				ResultSet rs = s.executeQuery(sql);	
				while(rs.next()){ 
				if (rs.getString("point") != null){  
					isPointExist= true;	
				}else{
					isPointExist= false;	
				}
				}
			}
			catch(Exception e){
				System.out.println(e);
			}
			return isPointExist;
	}
	
	public String accessRegionNationalFixe09(String operateur){
		
		String valeurAccessRegion = " ";
		
		try{
			String sql="SELECT access_region,"
						+"UPPER ( display_value ) display_value "
             			+"FROM ACCESS_REGION_VALUES "
  						+"WHERE UPPER(display_value) = '"+operateur+"' "
                        +"AND language_code = 2";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			while(rs.next()){
			valeurAccessRegion=rs.getString("access_region");
			}
			
		}
		catch(Exception e){
			System.out.println(e);
		}
		return valeurAccessRegion;
	}
	
	public String accessRegionNationalMobile(String operateur){
		
		String valeurAccessRegion = " ";
		
		try{
			String sql="SELECT access_region "
					+"FROM ACCESS_REGION_VALUES "
					+"WHERE display_value = '"+operateur+"' "
					+"AND language_code = 2";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);
			while(rs.next()){
			valeurAccessRegion = rs.getString("access_region");	
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return valeurAccessRegion;
	}
	
	public String pointRegionNational(String pointRegion){
		
		String valeurPointRegionNat = " ";
		
		try{
			String sql="SELECT point_region "
							+ "FROM POINT_REGION_VALUES "
							+ "WHERE display_value = '"+pointRegion+"' "
							+ "AND language_code = 2";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			while(rs.next()){
			valeurPointRegionNat = rs.getString("point_region");
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return valeurPointRegionNat;
	}
	
	public int zNENational(String zne){
		
		int valeurZNENat = 0;
		
		try{
			String sql="SELECT point_class "
						+ "FROM POINT_CLASS_VALUES "
						+ "WHERE display_value = '"+zne+"'"
						+ "AND language_code = 2";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			while(rs.next()){
			valeurZNENat = rs.getInt("point_class");
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return valeurZNENat;
	}
	
	
	//INTERNATIONAL
		
	//ComboBox Pays Mobile International
	public String accessRegionMobileInternational(String PaysMobile){
		
		String valeurPaysMobInter = " ";
		
		try{
			String sql="SELECT access_region "
						+"FROM ACCESS_REGION_VALUES "
	   					+"WHERE display_value = '"+PaysMobile+"' "
						+"AND language_code = 2";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);	
			while(rs.next()){
			valeurPaysMobInter = rs.getString("access_region");	
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return valeurPaysMobInter;
	}
	
	//ComboBox Pays Fixe International
	public String accessRegionFixeInternational(String PaysFixe){
		
		String valeurPaysFixeInter = " ";
		
		try{
			String sql="SELECT access_region "
						+"FROM ACCESS_REGION_VALUES "
	   					+"WHERE display_value = '"+PaysFixe+"' "
						+"AND language_code = 2";
			
			// Create a prepared statement
 			Statement s = con.createStatement();

			ResultSet rs = s.executeQuery(sql);
			while(rs.next()){
			valeurPaysFixeInter = rs.getString("access_region");
			}			
		}
		catch(Exception e){
			System.out.println(e);
		}
		return valeurPaysFixeInter;
	}
	
	
	//ComboBox Point Region International
	public String pointRegionFixeInternational(String pointRegion){
		
		String valeurPointRegionInter = " ";
		
		try{
			String sql="SELECT point_region "
						+ "FROM POINT_REGION_VALUES "
						+ "WHERE display_value = '"+pointRegion+"' "
						+ "AND language_code = 2";
			
			// Create a prepared statement
 			Statement s = con.createStatement();
			ResultSet rs = s.executeQuery(sql);	
			
			while(rs.next()){  
				valeurPointRegionInter = rs.getString("point_region");	
			}
		}
		catch(Exception e){
			System.out.println(e);
		}
		return valeurPointRegionInter;
	}
	
	//ComboBox Point Region International
		public String openPointZABPQ(String operateur,String point_region,int zNE, String pointZABPQ,Date date,String zLT,int entreprise,String typeAction){
			getConnection();
			String success="ok";
			try{
				DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd HH:mm:ss");				
				date.setSeconds(0);
				date.setMinutes(0);
				date.setHours(0);		
				String dat = dateFormat.format(date);
				System.out.println("MA DATE : "+dat);
				System.out.println("Appel à la procedure stocké OPEN_CONTACT_NUM");			
				CallableStatement cStmt = con.prepareCall("{call CPTL_CONFIG_CC.OPEN_CONTACT_NUM(?,?,?,?,?,?,?,?)}");				
			    cStmt.setString(1, pointZABPQ);
			    cStmt.setString(2, point_region);
			    System.out.println("MA DATE : "+dat);
			    cStmt.setString(3, dat);
			    cStmt.setInt(4, zNE);
			    cStmt.setString(5, zLT);
			    cStmt.setInt(6, entreprise);
			    cStmt.setString(7, operateur);
			    cStmt.setString(8, typeAction);
			    cStmt.executeUpdate();
			    			 
				System.out.println("OUVERTURE SUCCES");	
				
			}
			catch(Exception e){
				System.out.println(e);
				success="ko";
				JOptionPane.showMessageDialog(null, e, "Attention", JOptionPane.WARNING_MESSAGE);
			}
			return success;
		}
		
		//ComboBox Point Region International
		public String openPointZABPQplus1sec(String operateur,String point_region,int zNE, String pointZABPQ,Date date,String zLT,int entreprise,Timestamp dateInBase,String typeAction){
			getConnection();
			String success="ok";
			System.out.println("jappele celui décalé a une seconde");
			try{		
				DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
				if (date.getDate()==dateInBase.getDate()&& date.getMonth()==dateInBase.getMonth()&& date.getYear()==dateInBase.getYear()){
					System.out.println(" seconde + 1 de la date en base : ");
					date.setSeconds(dateInBase.getSeconds()+1);
					date.setMinutes(dateInBase.getMinutes());
					date.setHours(dateInBase.getHours());
				} else{
					date.setSeconds(0);
					date.setMinutes(0);
					date.setHours(0);
				}			
				String dat = dateFormat.format(date);
				System.out.println("MA DATE : "+dat);
				System.out.println("Appel à la procedure stocké OPEN_CONTACT_NUM");			
				CallableStatement cStmt = con.prepareCall("{call CPTL_CONFIG_CC.OPEN_CONTACT_NUM(?,?,?,?,?,?,?,?)}");				
			    cStmt.setString(1, pointZABPQ);
			    cStmt.setString(2, point_region);
			    System.out.println("MA DATE : "+dat);
			    cStmt.setString(3, dat);
			    cStmt.setInt(4, zNE);
			    cStmt.setString(5, zLT);
			    cStmt.setInt(6, entreprise);
			    cStmt.setString(7, operateur);
			    cStmt.setString(8, typeAction);
			    cStmt.executeUpdate();
			    			 
				System.out.println("OUVERTURE SUCCES");	
				
			}
			catch(Exception e){
				System.out.println(e);	
				success="ko";
				JOptionPane.showMessageDialog(null, e, "Attention", JOptionPane.WARNING_MESSAGE);					
			}
			return success;
		}
		
		public boolean isDateBefore(String indicatif,String pointZABPQ,Date date) throws ParseException{
			//Create Connection to the Oracle Database
			getConnection();
			boolean isDateBefore =false;
			Date dateBase =null;
			Date dateChosen=null;
			String dat2=null;
			try{
				DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
				String dat = dateFormat.format(date);
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			
				String sql="SELECT inactive_dt FROM USAGE_POINTS "
						+"WHERE point = '"+indicatif+pointZABPQ+"'";
				
				// Create a prepared statement
	 			Statement s = con.createStatement();
				ResultSet rs = s.executeQuery(sql);	
				
				while(rs.next()){ 			
					dat2=rs.getString("inactive_dt");		
				}
				
				System.out.println("ma date parsée : " +dat2);
				dateBase=sdf.parse(dat2);
				dateChosen = sdf.parse(dat);
				
				System.out.println("date base "+sdf.format(dateBase));
				System.out.println("date choisie "+sdf.format(dateChosen));

				if(dateBase.compareTo(dateChosen)>0){
					System.out.println("date inferieur à celui en base");
					isDateBefore= true;
				}else if(dateBase.compareTo(dateChosen)<=0){
					System.out.println("date superieur à celui en base");
					isDateBefore= false;
				}
			}
			catch(Exception e){
				System.out.println(e);
			}
			return isDateBefore;
		}
		
		public Timestamp dateInBase(String indicatif,String pointZABPQ) throws ParseException{
			//Create Connection to the Oracle Database
			getConnection();
			Timestamp date =null;
			try{
				System.out.println("get Date");
				
				String sql="SELECT inactive_dt FROM USAGE_POINTS "
						+"WHERE point = '"+indicatif+pointZABPQ+"'";
				
				// Create a prepared statement
	 			Statement s = con.createStatement();
				ResultSet rs = s.executeQuery(sql);	
				
				while(rs.next()){ 			
					date=rs.getTimestamp("inactive_dt");		
				}
				if (rs.getString("inactive_dt")==null || rs.getString("inactive_dt").isEmpty()){
					date=null;
				}
				System.out.println("date en base : "+date);
			}
			catch(Exception e){
				System.out.println(e);
			}
			return date;
		}
		
		//Verification de l'existance d'un point_class_ref
		public boolean isPointClassRefExist(String operateur){
			getConnection();
				boolean isPointClassRefExist =false;
				try{
					String sql="SELECT * FROM POINT_CLASS_VALUES "
							+"WHERE UPPER(display_value) = '"+operateur+"' ";
										
					// Create a prepared statement
		 			Statement s = con.createStatement();

					ResultSet rs = s.executeQuery(sql);	
					while(rs.next()){ 
					if (rs.getString("display_value") != null){  
						isPointClassRefExist= true;	
					}else{
						isPointClassRefExist= false;	
					}
					}
				}
				catch(Exception e){
					System.out.println(e);
				}
				return isPointClassRefExist;
		}
		
}
	
